-- 10/12/2013
ALTER TABLE supplierproduct
  ADD COLUMN id integer DEFAULT nextval('supplierproductseq'::regclass);
  
ALTER TABLE supplierproduct
  ADD PRIMARY KEY (id);
  
  
  
  -- 18/12/2013
ALTER TABLE traders
  ADD COLUMN idunico integer DEFAULT nextval('tradersseq'::regclass);
  
ALTER TABLE traders
  ADD PRIMARY KEY (idunico);
  
  
  --18/01/2014
ALTER TABLE reqshipfuelassdetail ADD COLUMN idmaster integer NOT NULL DEFAULT nextval('reqshipfuelassdetailseq'::regclass);
ALTER TABLE reqshipfuelassdetail ADD PRIMARY KEY (idmaster);

--24/01/2014
ALTER TABLE reqshipfuelassignement
  ADD COLUMN id integer NOT NULL DEFAULT nextval('reqshipfuelassignementseq'::regclass);
ALTER TABLE reqshipfuelassignement
  ADD PRIMARY KEY (id);
  
  
  -- query modificata per invitation for a quote
  /*
  
select distinct ON (reqshipfuelassdetail.id) reqshipfuelassdetail.id, reqshipfuelassdetail.seq, reqshipfuelassdetail.productname, reqshipfuelassdetail.productid, reqshipfuelassdetail.quantity, reqshipfuelassdetail.um, reqshipfuelassdetail.id as reqid, reqshipfuelassdetail.stat, status.description, reqshipfuelass.vesselname, reqshipfuelass.porttoref, reqshipfuelassdetail.note, reqshipfuelassdetail.id, reqquote.suppid, suppliers.companyname 
from (((reqshipfuelass left join status as status_1 on reqshipfuelass.status = status_1.status) 
right join (reqshipfuelassdetail 
left join status on reqshipfuelassdetail.stat = status.status) on reqshipfuelass.id = reqshipfuelassdetail.id) 
left join reqquote on (reqshipfuelassdetail.id = reqquote.reqid) and (reqshipfuelassdetail.seq = reqquote.reqseq)) 
left join suppliers on reqquote.suppid = suppliers.supplid
where (((reqshipfuelassdetail.stat)='SE' or (reqshipfuelassdetail.stat)='iq'))
order by reqshipfuelassdetail.id, reqshipfuelassdetail.seq;
  
  
  */
  
  
 --20/3/2014
  ALTER TABLE reqquote ADD COLUMN id integer NOT NULL DEFAULT nextval('reqquoteseq'::regclass);
  ALTER TABLE reqquote ADD PRIMARY KEY(id);
  
  
  ALTER TABLE notesupp ADD COLUMN idmaster integer NOT NULL DEFAULT nextval('notesuppseq'::regclass);
  ALTER TABLE notesupp ADD PRIMARY KEY(idmaster);
  
  
 --24/3/2014
  CREATE SEQUENCE notesuppseqseq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
  
  
 CREATE TABLE notesuppseqt
(
  id integer NOT NULL DEFAULT nextval('notesuppseqseq'::regclass),
  idrequest integer,
  idsupplier integer,
  seq integer,
  note text,
  CONSTRAINT notesuppseqb_pkey PRIMARY KEY (id)
);
  


-- 26/03/2014
UPDATE reqshipfuelass set notea = '';


 CREATE TABLE configuration
(
  id integer NOT NULL DEFAULT 1,
  agentnomination text,
  CONSTRAINT configurationb_pkey PRIMARY KEY (id)
);

INSERT INTO configuration (id, agentnomination) values(1, '');


-- 31/03/2014
ALTER TABLE configuration ADD COLUMN agentinformation TEXT;


-- 4/04/2014
/*

select min(id) from reqshipfuelass where extract( year from deliverydt) >=2013 
3746

DELETE FROM reqshipfuelass where id < 3746
DELETE FROM reqshipfuelassdetail where id < 3746
DELETE FROM reqshipfuelassignement where ref < 3746
DELETE FROM reqquote WHERE reqid < 3746
DELETE FROM notesupp WHERE id < 3746


*/


-- 8/9/2014  

ALTER TABLE suppinvoicedetail
   ALTER COLUMN partno TYPE character varying(50);


ALTER TABLE suppinvoicedetail ADD COLUMN idmaster integer DEFAULT nextval('suppinvoicedetailseq'::regclass);

ALTER TABLE suppinvoicedetail
  ADD PRIMARY KEY (idmaster);
  
  
-- 10/9/2014
SELECT setval('public.custinvoicedetailseq', (select max(id)+1 as valore from custinvoicedetail), true);



-- 29/11/2014
DROP TABLE custinvoicedetail_bk;
DROP TABLE custinvoicedetail_copia;
DROP TABLE custinvoicedetail_old;
DROP TABLE cambioanda_old;
DROP TABLE errori_di_incollamento;
DROP TABLE errori_di_salvataggio_correzione_automatica_nome;
DROP TABLE portssuppliers_bk;


--7/12/2014
CREATE SEQUENCE invoicecounterseq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
  
  
ALTER TABLE invoicecounter ADD COLUMN id integer NOT NULL DEFAULT nextval('invoicecounterseq'::regclass);
ALTER TABLE invoicecounter ADD PRIMARY KEY (id);

-- 9/12/2014
ALTER TABLE vatarticles ADD COLUMN id integer NOT NULL DEFAULT nextval('vatarticlesseq'::regclass);
ALTER TABLE vatarticles ADD PRIMARY KEY (id);


--9/1/2015
CREATE OR REPLACE FUNCTION inserisco_ruoli()
  RETURNS trigger AS
$BODY$
BEGIN

IF (TG_OP = 'INSERT') THEN
  -- inserisce i dati nella tabella user_roles quando inserito un utente
  INSERT INTO user_roles SELECT NEW.user_name, 'UserPico';
	RETURN NEW;
ELSIF (TG_OP = 'UPDATE') THEN
  -- aggiorna i dati del utente in user_roles quando modificato in users
  UPDATE user_roles SET user_name = NEW.user_name WHERE user_name=OLD.user_name;
  RETURN NEW;
ELSIF (TG_OP = 'DELETE') THEN
  -- cancella i dati del utente in user_roles quando cancellato in users
  DELETE FROM user_roles WHERE user_name = OLD.user_name;
  RETURN OLD;
END IF;

END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

CREATE SEQUENCE usersseq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

CREATE TABLE users
(
  id integer DEFAULT nextval(('usersseq'::text)::regclass),
  user_name character(50) NOT NULL,
  user_pass character(40) NOT NULL,
  ultimocollegamento timestamp without time zone,
  ippub character(100),
  iploc character(100),
  scadenza timestamp without time zone,
  email character(50),
  ultimologin timestamp without time zone,
  idsessione character varying(50),
  scollegare boolean DEFAULT false,
  CONSTRAINT users_pkey PRIMARY KEY (user_name),
  CONSTRAINT users_id_key UNIQUE (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE users
  OWNER TO postgres;



CREATE TABLE user_roles
(
  user_name character(50) NOT NULL,
  role_name character varying(30) NOT NULL,
  CONSTRAINT user_roles_pkey PRIMARY KEY (user_name, role_name)
)
WITH (
  OIDS=FALSE
);


CREATE TRIGGER agg_ruoli
  AFTER INSERT OR UPDATE OR DELETE
  ON users
  FOR EACH ROW
  EXECUTE PROCEDURE inserisco_ruoli();

